create  table jms_dm.dm_terminal_tran_direct_delivery_send_sum_dt
(
end_center_code         varchar(100) COMMENT '末端中心code' ,
end_provider_id         varchar(100) COMMENT '省份id' ,
end_city_id             varchar(100) COMMENT '城市id' ,
final_sign_network_code varchar(100) COMMENT '规划签收网点code' ,
end_provider_name       varchar(100) COMMENT '省名字' ,
end_agent_code          varchar(100) COMMENT '代理区code' ,
end_agent_name          varchar(100) COMMENT '代理区名字' ,
end_center_name         varchar(100) COMMENT '末端中心名字' ,
end_city_name           varchar(100) COMMENT '末端城市' ,
end_area_id             varchar(100) COMMENT '末端区县id' ,
end_area_name           varchar(100) COMMENT '末端区县' ,
final_sign_network_name varchar(100) COMMENT '规划签收网点' ,
package_type            varchar(100) COMMENT '包类型' ,
second_package_code     varchar(100) COMMENT '二段吗' ,
third_package_code      varchar(100) COMMENT '三段码' ,
waybill_sum             bigint COMMENT '运单量' ,
package_sum             bigint COMMENT '包数' ,
third_code_right_sum    bigint COMMENT '三段码准确的量' ,
arrival_send_sum        bigint COMMENT '一键到出量' ,
aging_sign_sum          bigint COMMENT '准点签收量' ,
ass_sum                 bigint COMMENT '错分量' ,
send_noarrival_sum      bigint COMMENT '有发无到总量' ,
center_send_sign_hours  decimal(16,2) COMMENT '中心发发件时间到时效签收时间' ,
no_send_sign_cnt        bigint COMMENT '发签缺失量' ,
deliver_sign_hours      decimal(16,2) COMMENT '派签时长' ,
deliver_sign_loss_cnt   bigint COMMENT '派签缺失量' ,
date_time               date COMMENT '业务日期',
end_franchisee_code varchar(100) comment  '加盟商code',
end_franchisee_name varchar(100) comment  '加盟商'
)
ENGINE=OLAP
DUPLICATE KEY(end_center_code,end_provider_id,end_city_id,final_sign_network_code)
comment '转运直送发件维度汇总'
PARTITION BY RANGE (date_time) (
   START ("2022-10-08") END ("2022-12-07") EVERY (INTERVAL 1 day))DISTRIBUTED BY HASH(third_package_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);
